package com.suredy.app.project.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.SimpleExpression;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.suredy.Constants;
import com.suredy.app.project.constants.ProjectStatus;
import com.suredy.app.project.model.Project;
import com.suredy.core.service.BaseSrvWithEntity;

@Service
public class ProjectSrv extends BaseSrvWithEntity<Project> {

	@Value("${project.list.count}")
	private String SQL_LIST_COUNT;

	@Value("${report.select}")
	private String SQL_REPORTS;

	public ProjectSrv() {
		this.addAnDefOrder("no", true);
	}

	@Override
	public DetachedCriteria getDc(Project t) {
		DetachedCriteria dc = super.getDc(t);

		if (t == null)
			return dc;

		if (!StringUtils.isBlank(t.getId())) {
			dc.add(Restrictions.eq("id", t.getId()));
		}
		if (!StringUtils.isBlank(t.getNo())) {
			dc.add(Restrictions.eq("no", t.getNo()));
		}
		if (!StringUtils.isBlank(t.getName())) {
			dc.add(Restrictions.like("name", t.getName(), MatchMode.ANYWHERE));
		}
		if (t.getStatus() != null) {
			dc.add(Restrictions.eq("status", t.getStatus()));
		}

		return dc;
	}

	@SuppressWarnings("unchecked")
	public List<Project> getForListSearch(Integer page, Integer pageSize, String key) {
		page = page == null || page <= 0 ? 1 : page;
		pageSize = pageSize == null || pageSize <= 0 ? Constants.DEFAULT_PAGE_SIZE : pageSize;

		DetachedCriteria dc = super.getDc(null);

		if (!StringUtils.isBlank(key)) {
			key = key.trim();

			SimpleExpression no = Restrictions.like("no", key, MatchMode.ANYWHERE);
			SimpleExpression name = Restrictions.like("name", key, MatchMode.ANYWHERE);

			dc.add(Restrictions.or(no, name));
		}

		dc.addOrder(Order.asc("no"));

		return (List<Project>) this.readByCriteria(dc, page, pageSize);
	}

	public int countForListSearch(String key) {
		DetachedCriteria dc = super.getDc(null);

		if (!StringUtils.isBlank(key)) {
			key = key.trim();

			SimpleExpression no = Restrictions.like("no", key, MatchMode.ANYWHERE);
			SimpleExpression name = Restrictions.like("name", key, MatchMode.ANYWHERE);

			dc.add(Restrictions.or(no, name));
		}

		return this.getCountByCriteria(dc);
	}

	public Map<String, Float> totalForListSearch(String key) {
		if (StringUtils.isBlank(this.SQL_LIST_COUNT))
			return null;

		List<Object> params = new ArrayList<Object>();

		// %WHERE%
		String where = "";
		if (!StringUtils.isBlank(key)) {
			where = "WHERE a.`no` LIKE ? OR a.`name` LIKE ? ";
			params.add("%" + key + "%");
			params.add("%" + key + "%");
		}

		String sql = this.SQL_LIST_COUNT.replace("%WHERE%", where);

		Object[] data = null;

		if (params.isEmpty()) {
			data = (Object[]) this.readSingleBySQL(sql);
		} else {
			data = (Object[]) this.readSingleBySQL(sql, params.toArray());
		}

		if (data == null || data.length != 4)
			return null;

		Map<String, Float> ret = new HashMap<String, Float>();

		ret.put("price", data[0] == null ? 0F : ((Double) data[0]).floatValue());
		ret.put("payPrice", data[1] == null ? 0F : ((Double) data[1]).floatValue());
		ret.put("payIn", data[2] == null ? 0F : ((Double) data[2]).floatValue());
		ret.put("payOut", data[3] == null ? 0F : ((Double) data[3]).floatValue());

		return ret;
	}

	@Transactional
	public int delete(String[] ids) {
		if (ids == null || ids.length <= 0)
			return 0;

		int count = 0;

		for (String id : ids) {
			Project p = this.get(id);

			if (p != null) {
				this.delete(p);
				count++;
			}
		}

		return count;
	}

	public Map<ProjectStatus, Integer> getProjectStatusCount(Date start, Date end) {
		Map<ProjectStatus, Integer> ret = new LinkedHashMap<ProjectStatus, Integer>();

		for (ProjectStatus status : ProjectStatus.values()) {
			ret.put(status, 0);
		}

		String ql = "SELECT T.status, COUNT(T) FROM Project T WHERE 1=1";

		List<Object> params = new ArrayList<Object>();

		if (start != null && end == null) {
			ql += " AND T.start>=?";
			params.add(start);
		}

		if (start == null && end != null) {
			ql += " AND T.end<?";
			params.add(end);
		}

		if (start != null && end != null) {
			ql += " AND ((T.start>=? AND T.start<?) OR (T.end>=? AND T.end<?))";
			params.add(start);
			params.add(end);
			params.add(start);
			params.add(end);
		}

		ql += " GROUP BY T.status";

		List<?> data = this.readByQL(ql, params.toArray());

		if (data == null || data.isEmpty())
			return ret;

		for (Object obj : data) {
			Object[] tmp = (Object[]) obj;

			if (tmp[0] == null)
				continue;

			ProjectStatus status = ProjectStatus.valueOf(tmp[0].toString());
			int count = ((Long) tmp[1]).intValue();

			ret.put(status, count);
		}

		return ret;
	}

	public Map<String, Float> getProjectOutputValue(Integer year) {
		if (StringUtils.isBlank(this.SQL_LIST_COUNT))
			return null;

		List<Object> params = new ArrayList<Object>();

		// %WHERE%
		String where = "";
		if (year != null) {
			String start = year + "-1-1";
			String end = (year + 1) + "-1-1";
			where += "WHERE ((a.start>=? AND a.start<?) OR (a.end>=? AND a.end<?)) ";
			params.add(start);
			params.add(end);
			params.add(start);
			params.add(end);
		}

		String sql = this.SQL_LIST_COUNT.replace("%WHERE%", where);

		Object[] data = null;

		if (params.isEmpty()) {
			data = (Object[]) this.readSingleBySQL(sql);
		} else {
			data = (Object[]) this.readSingleBySQL(sql, params.toArray());
		}

		if (data == null || data.length != 4)
			return null;

		Map<String, Float> ret = new HashMap<String, Float>();

		ret.put("price", data[0] == null ? 0F : ((Double) data[0]).floatValue());
		ret.put("payPrice", data[1] == null ? 0F : ((Double) data[1]).floatValue());
		ret.put("payIn", data[2] == null ? 0F : ((Double) data[2]).floatValue());
		ret.put("payOut", data[3] == null ? 0F : ((Double) data[3]).floatValue());

		return ret;
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getReportData(Integer year, Integer month, ProjectStatus status, String customerCompany, String manager, String type, String obtain) {
		if (StringUtils.isBlank(this.SQL_REPORTS))
			return null;

		String where = " WHERE 1=1";

		List<Object> params = new ArrayList<Object>();

		if (year != null) {
			String start = year + (month != null ? "-" + month + "-1" : "");
			String end = (month == null ? year + 1 : year) + (month != null ? "-" + (month + 1) + "-1" : "");
			where += " AND ((a.`start` >= ? and a.`start` < ?) OR (a.`end` >= ? and a.`end` < ?))";
			params.add(start);
			params.add(end);
			params.add(start);
			params.add(end);
		}

		if (status != null) {
			where += " AND a.`status` = ?";
			params.add(status.name());
		}

		if (!StringUtils.isBlank(customerCompany)) {
			where += " AND a.customerCompany = ?";
			params.add(customerCompany);
		}

		if (!StringUtils.isBlank(manager)) {
			where += " AND c.user_id = ?";
			params.add(manager);
		}

		if (!StringUtils.isBlank(type)) {
			where += " AND a.type = ?";
			params.add(type);
		}

		if (!StringUtils.isBlank(obtain)) {
			where += " AND a.obtainType = ?";
			params.add(obtain);
		}

		List<Object[]> data = (List<Object[]>) this.readBySQL(this.SQL_REPORTS + where, params.toArray());

		if (data == null)
			return null;

		List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();

		for (Object[] d : data) {
			Map<String, Object> item = new HashMap<String, Object>();

			item.put("price", d[0] == null ? 0f : d[0]);
			item.put("payPrice", d[1] == null ? 0f : d[1]);
			item.put("payIn", d[2] == null ? 0d : d[2]);
			item.put("payOut", d[3] == null ? 0d : d[3]);
			item.put("manager", d[4] == null ? "未知" : d[4]);
			item.put("customerCompany", d[5] == null ? "未知" : d[5]);
			item.put("status", d[6] == null ? "未知" : ProjectStatus.valueOf(d[6].toString()));
			item.put("type", d[7] == null ? "未知" : d[7]);
			item.put("obtainType", d[8] == null ? "未知" : d[8]);

			ret.add(item);
		}

		return ret.isEmpty() ? null : ret;
	}

}
